﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QuanLyThongTinNhanKhau.model.DAO
{
    class HoKhauDAO
    {
        SQLServiceDAO service = new SQLServiceDAO();

        internal DataTable loadTable()
        {
            DataTable dt = new DataTable();
            String sql = "select * from SOHOKHAU where TinhTrang = 'Enable'";
            dt = service.getDataTable(sql);
            return dt;
        }

        internal DataTable btnTimKiem(string maHoKhau, string tenChuHo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from SOHOKHAU where TinhTrang = 'Enable'");

            if (maHoKhau != "")
            {
                sb.Append(" and MaHoKhau Like N'%" + maHoKhau + "%'");
            }

            if (tenChuHo != "")
            {
                sb.Append(" and TenChuHo LIKE N'%" + tenChuHo + "%'");
            }

            DataTable dt = service.getDataTable(sb.ToString());
            return dt;
        }

        internal bool isValidHoKhau(string str)
        {
            String sql = "Select TOP(1) * from SoHoKhau where MaHoKhau = '" + str + "'";
            DataTable dt = service.getDataTable(sql);

            if (dt.Rows.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        internal bool newSoHoKhau(string maHoKhau, string tenChuHo, string ngayCap)
        {
            String sql = "insert into SOHOKHAU values ('" + maHoKhau + "','" + ngayCap + "',N'" + tenChuHo + "','admin','Enable')";
            return service.executeNonQuery(sql);
        }

        internal bool updateSoHoKhau(string maHoKhau, string tenChuHo, string ngayCap)
        {
            String sql = "update SOHOKHAU set TenChuHo = N'" + tenChuHo + "', NgayCap = '" + ngayCap + "' where MaHoKhau = '" + maHoKhau + "'";
            return service.executeNonQuery(sql);
        }

        internal bool deleteHoKhau(string maHoKhau)
        {
            String sql = "update SOHOKHAU set TinhTrang = 'Disable' where MaHoKhau = '" + maHoKhau + "'";
            return service.executeNonQuery(sql);
        }

        internal DataTable loadNhanKhau(string maHoKhau)
        {
            DataTable dt = new DataTable();
            String sql = "select n.MaNhanKhau, HoTen, NgaySinh, GioiTinh, CMND, QueQuan, QuanHeChuHo from SOHOKHAU s join QUANHE q on s.MaHoKhau = q.MaHoKhau join NHANKHAU n on q.MaNhanKhau = n.MaNhanKhau where s.TinhTrang = 'Enable' and n.TinhTrang = 'Enable' and s.MaHoKhau = '" + maHoKhau + "'";
            dt = service.getDataTable(sql);
            return dt;
        }

        internal bool deleteNhanKhau(string maHoKhau, string maNhanKhau)
        {
            String sql = "Delete from QUANHE where MaHoKhau = '" + maHoKhau + "' and MaNhanKhau = '" + maNhanKhau + "'";
            return service.executeNonQuery(sql);
        }
    }
}
